package com.example.service;

import com.example.mapper.SqlExecutionMapper;
import com.example.mapper.SqlHistoryMapper;
import com.example.model.DbConnection;
import com.example.model.SqlExecution;
import com.example.model.SqlHistory;
import com.example.util.HashUtil;
import com.example.util.RedisCacheUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestTemplate;

import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
@Slf4j
public class SqlTranslateService {
    @Autowired
    private SqlHistoryMapper sqlHistoryMapper;
    
    @Autowired
    private SqlExecutionMapper sqlExecutionMapper;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private MetadataService metadataService;
    
    @Autowired
    private DbConnectionService dbConnectionService;
    
    @Autowired
    private RedisCacheUtil redisCacheUtil;
    
    @Autowired
    private LockService lockService;

    @Autowired
    private CacheMetricsCollector cacheMetricsCollector;

    @Value("${ai.api.key}")
    private String apiKey;
    
    @Value("${cache.translate.expireTime:3600}")
    private long translateCacheExpireTime;
    
    @Value("${cache.sql.expireTime:1800}")
    private long sqlCacheExpireTime;

    private final RestTemplate restTemplate = new RestTemplate();
    // 通义千问API endpoint
    private static final String API_URL = "https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions";

    private static final Set<String> FORBIDDEN_KEYWORDS = Stream.of(
            "DELETE", "UPDATE", "INSERT", "DROP", "ALTER", "TRUNCATE",
            "EXEC", "SHUTDOWN", "CREATE", "GRANT", "REVOKE"
    ).collect(Collectors.toCollection(LinkedHashSet::new));

    private static final int MAX_ROWS = 1000;
    private static final Pattern SQL_COMMENT_PATTERN = Pattern.compile("--|\\/\\*");
    
    // 缓存相关常量
    private static final String TRANSLATE_CACHE_PREFIX = "translate:";
    private static final String SQL_RESULT_CACHE_PREFIX = "sql_result:";

    /**
     * 使用默认数据库连接转换SQL
     */
    public String translateToSql(String text, Long userId) {
        return translateToSql(text, userId, null);
    }

    /**
     * 使用指定数据库连接转换SQL
     */
    public String translateToSql(String text, Long userId, Long connectionId) {
        String sqlResult;
        long startTime = System.currentTimeMillis();
        boolean cacheHit = false;
        
        try {
            // 生成缓存键
            String cacheKey = generateTranslateCacheKey(text, connectionId);
            
            // 尝试从缓存获取
            Object cachedResult = redisCacheUtil.get(cacheKey);
            if (cachedResult != null) {
                log.debug("翻译缓存命中: {}", cacheKey);
                sqlResult = (String) cachedResult;
                cacheHit = true;
            } else {
                // 缓存未命中，调用AI API
                log.debug("翻译缓存未命中，调用AI API: {}", cacheKey);
                sqlResult = callAiApi(text, userId, connectionId);
                
                // 将结果存入缓存
                redisCacheUtil.set(cacheKey, sqlResult, translateCacheExpireTime, TimeUnit.SECONDS);
                log.debug("翻译结果已缓存: {}，过期时间: {}秒", cacheKey, translateCacheExpireTime);
            }

            // 保存历史记录
            SqlHistory history = new SqlHistory();
            history.setUserId(userId);
            history.setInputText(text);
            history.setSqlResult(sqlResult);
            history.setDbConnectionId(connectionId);
            sqlHistoryMapper.insert(history);

            return sqlResult;
        } finally {
            // 记录缓存访问统计
            long loadTime = System.currentTimeMillis() - startTime;
            // 使用连接ID或默认ID 0
            Long connId = connectionId != null ? connectionId : 0L;
            cacheMetricsCollector.recordCacheAccess(connId, cacheHit, loadTime);
        }
    }
    
    /**
     * 生成翻译缓存键
     */
    private String generateTranslateCacheKey(String text, Long connectionId) {
        String textHash = HashUtil.md5(text);
        return TRANSLATE_CACHE_PREFIX + textHash + ":" + (connectionId != null ? connectionId : "default");
    }

    public List<SqlHistory> getUserHistory(Long userId) {
        return sqlHistoryMapper.findByUserId(userId);
    }

    /**
     * 分页获取指定用户的历史记录
     * @param userId 用户ID
     * @param page 页码（从1开始）
     * @param pageSize 每页记录数
     * @return 分页后的历史记录
     */
    public List<SqlHistory> getUserHistoryPaged(Long userId, int page, int pageSize) {
        // 计算偏移量
        int offset = (page - 1) * pageSize;
        return sqlHistoryMapper.findByUserIdPaged(userId, offset, pageSize);
    }

    /**
     * 获取指定用户的历史记录总数
     * @param userId 用户ID
     * @return 记录总数
     */
    public int getUserHistoryCount(Long userId) {
        return sqlHistoryMapper.countByUserId(userId);
    }

    // 获取所有用户的历史记录
    public List<SqlHistory> getAllHistory() {
        return sqlHistoryMapper.findAll();
    }

    /**
     * 分页获取历史记录
     * @param page 页码（从1开始）
     * @param pageSize 每页记录数
     * @return 分页后的历史记录
     */
    public List<SqlHistory> getPagedHistory(int page, int pageSize) {
        // 计算偏移量
        int offset = (page - 1) * pageSize;
        return sqlHistoryMapper.findAllPaged(offset, pageSize);
    }
    
    /**
     * 获取历史记录总数
     * @return 记录总数
     */
    public int getHistoryCount() {
        return sqlHistoryMapper.count();
    }

    // 获取总查询数量
    public int getTotalQueriesCount() {
        return sqlHistoryMapper.count();
    }
    
    /**
     * 获取SQL执行记录
     */
    public List<SqlExecution> getUserExecutions(Long userId, Integer limit) {
        return sqlExecutionMapper.findByUserId(userId, limit);
    }
    
    /**
     * 获取所有用户的SQL执行记录
     */
    public List<SqlExecution> getAllExecutions(Integer limit) {
        return sqlExecutionMapper.findAll(limit);
    }
    
    /**
     * 分页获取SQL执行记录
     * @param page 页码（从1开始）
     * @param pageSize 每页记录数
     * @return 分页后的执行记录
     */
    public List<SqlExecution> getPagedExecutions(int page, int pageSize) {
        // 计算偏移量
        int offset = (page - 1) * pageSize;
        return sqlExecutionMapper.findAllPaged(offset, pageSize);
    }
    
    /**
     * 获取SQL执行记录总数
     * @return 记录总数
     */
    public int getExecutionsCount() {
        return sqlExecutionMapper.count();
    }
    
    /**
     * 获取SQL执行统计信息
     */
    public Map<String, Object> getExecutionStatistics() {
        Map<String, Object> stats = new HashMap<>();
        stats.put("totalExecutions", sqlExecutionMapper.count());
        stats.put("successfulExecutions", sqlExecutionMapper.countSuccessful());
        stats.put("failedExecutions", sqlExecutionMapper.countFailed());
        return stats;
    }

    private String callAiApi(String text, Long userId, Long connectionId) {
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_JSON);
        headers.setBearerAuth(apiKey);

        // 获取数据库结构信息
        String schemaInfo;
        if (connectionId != null) {
            schemaInfo = metadataService.getSchemaDescription(connectionId, userId);
        } else {
            schemaInfo = metadataService.getDefaultSchemaDescription();
        }

        String systemPrompt = String.format(
                "你是一个SQL专家。请根据用户的自然语言描述生成对应的SQL查询语句。\n\n" +
                        "%s\n\n" +
                        "请注意：\n" +
                        "1. 只返回SQL语句，不要包含任何解释\n" +
                        "2. 使用实际存在的表名和字段名\n" +
                        "3. 确保SQL语法正确",
                schemaInfo
        );

        // 构建请求体
        Map<String, Object> requestBody = new HashMap<>();
        requestBody.put("model", "qwen-plus");

        // 构建消息列表的正确方式
        List<Map<String, Object>> messages = new ArrayList<>();

        // 系统提示
        Map<String, Object> systemMessage = new LinkedHashMap<>();
        systemMessage.put("role", "system");
        systemMessage.put("content", systemPrompt);
        messages.add(systemMessage);

        // 用户输入
        Map<String, Object> userMessage = new LinkedHashMap<>();
        userMessage.put("role", "user");
        userMessage.put("content", text);
        messages.add(userMessage);
        
        requestBody.put("messages", messages);
        requestBody.put("temperature", 0.7);

        HttpEntity<Map<String, Object>> request = new HttpEntity<>(requestBody, headers);

        try {
            Map<String, Object> response = restTemplate.postForObject(API_URL, request, Map.class);
            return extractSqlFromResponse(response);
        } catch (Exception e) {
            throw new RuntimeException("AI服务调用失败: " + e.getMessage());
        }
    }

    private String extractSqlFromResponse(Map<String, Object> response) {
        try {
            List<Map<String, Object>> choices = (List<Map<String, Object>>) response.get("choices");
            Map<String, Object> message = (Map<String, Object>) choices.get(0).get("message");
            return (String) message.get("content");
        } catch (Exception e) {
            throw new RuntimeException("解析AI响应失败");
        }
    }

    /**
     * 使用默认数据库连接执行SQL
     */
    public Map<String, Object> executeSql(String sql, Long userId) {
        Objects.requireNonNull(sql, "SQL语句不能为空");
        if (sql.trim().isEmpty()) {
            throw new IllegalArgumentException("SQL内容不能为空");
        }

        // 记录执行信息
        SqlExecution execution = new SqlExecution();
        execution.setUserId(userId);
        execution.setConnectionId(null); // 默认连接
        execution.setConnectionName("系统默认连接");
        execution.setSqlText(sql);
        execution.setExecutedAt(LocalDateTime.now());
        
        long startTime = System.currentTimeMillis();
        
        try {
            validateSql(sql);
            
            // 尝试从缓存获取结果
            String sqlCacheKey = generateSqlCacheKey(sql, null);
            Object cachedResult = redisCacheUtil.get(sqlCacheKey);
            
            Map<String, Object> result;
            if (cachedResult != null && isCacheable(sql)) {
                log.debug("SQL执行结果缓存命中: {}", sqlCacheKey);
                result = (Map<String, Object>) cachedResult;
                // 添加缓存标记
                result.put("fromCache", true);
            } else {
                // 执行SQL查询
                result = executeSafeQuery(sql, jdbcTemplate);
                
                // 如果是可缓存的查询，将结果存入缓存
                if (isCacheable(sql)) {
                    redisCacheUtil.set(sqlCacheKey, result, sqlCacheExpireTime, TimeUnit.SECONDS);
                    log.debug("SQL执行结果已缓存: {}，过期时间: {}秒", sqlCacheKey, sqlCacheExpireTime);
                }
            }
            
            // 记录成功执行信息
            long endTime = System.currentTimeMillis();
            execution.setIsSuccess(true);
            execution.setExecutionTimeMs(endTime - startTime);
            execution.setRowCount(getRowCount(result));
            sqlExecutionMapper.insert(execution);
            
            return result;
        } catch (Exception e) {
            // 记录失败执行信息
            long endTime = System.currentTimeMillis();
            execution.setIsSuccess(false);
            execution.setExecutionTimeMs(endTime - startTime);
            execution.setErrorMessage(e.getMessage());
            sqlExecutionMapper.insert(execution);
            
            if (e instanceof DataAccessException) {
                throw new QueryExecutionException("查询执行失败: " + e.getCause().getMessage(), e);
            } else {
                throw e;
            }
        }
    }
    
    /**
     * 判断SQL是否可缓存
     */
    private boolean isCacheable(String sql) {
        String normalizedSql = sql.toUpperCase().trim();
        
        // 只缓存SELECT语句
        if (!normalizedSql.startsWith("SELECT")) {
            return false;
        }
        
        // 不缓存包含随机函数的查询
        if (normalizedSql.contains("RAND(") || normalizedSql.contains("NOW(") || 
            normalizedSql.contains("CURRENT_TIMESTAMP") || normalizedSql.contains("UUID()")) {
            return false;
        }
        
        return true;
    }
    
    /**
     * 获取结果集行数
     */
    private int getRowCount(Map<String, Object> result) {
        if (result.containsKey("data") && result.get("data") instanceof List) {
            return ((List<?>) result.get("data")).size();
        }
        return 0;
    }
    
    /**
     * 生成SQL执行结果缓存键
     */
    private String generateSqlCacheKey(String sql, Map<String, Object> params) {
        String sqlHash = HashUtil.sqlHash(sql);
        String paramsHash = params != null ? HashUtil.paramsHash(params) : "no_params";
        return SQL_RESULT_CACHE_PREFIX + sqlHash + ":" + paramsHash;
    }
    
    /**
     * 使用指定数据库连接执行SQL
     */
    public Map<String, Object> executeSql(String sql, Long connectionId, Long userId) {
        Objects.requireNonNull(sql, "SQL语句不能为空");
        if (sql.trim().isEmpty()) {
            throw new IllegalArgumentException("SQL内容不能为空");
        }
        Objects.requireNonNull(connectionId, "数据库连接ID不能为空");

        // 获取数据库连接
        DbConnection connection = dbConnectionService.getConnectionById(connectionId, userId);
        
        // 记录执行信息
        SqlExecution execution = new SqlExecution();
        execution.setUserId(userId);
        execution.setConnectionId(connectionId);
        execution.setConnectionName(connection.getName());
        execution.setSqlText(sql);
        execution.setExecutedAt(LocalDateTime.now());
        
        long startTime = System.currentTimeMillis();
        
        try {
            validateSql(sql);
            
            // 尝试从缓存获取结果
            Map<String, Object> params = new HashMap<>();
            params.put("connectionId", connectionId);
            String sqlCacheKey = generateSqlCacheKey(sql, params);
            Object cachedResult = redisCacheUtil.get(sqlCacheKey);
            
            Map<String, Object> result;
            if (cachedResult != null && isCacheable(sql)) {
                log.debug("SQL执行结果缓存命中: {}", sqlCacheKey);
                result = (Map<String, Object>) cachedResult;
                // 添加缓存标记
                result.put("fromCache", true);
            } else {
                // 获取JdbcTemplate
                JdbcTemplate jdbcTemplate = metadataService.getJdbcTemplate(connection);
                
                // 执行SQL查询
                result = executeSafeQuery(sql, jdbcTemplate);
                
                // 如果是可缓存的查询，将结果存入缓存
                if (isCacheable(sql)) {
                    redisCacheUtil.set(sqlCacheKey, result, sqlCacheExpireTime, TimeUnit.SECONDS);
                    log.debug("SQL执行结果已缓存: {}，过期时间: {}秒", sqlCacheKey, sqlCacheExpireTime);
                }
            }
            
            // 记录成功执行信息
            long endTime = System.currentTimeMillis();
            execution.setIsSuccess(true);
            execution.setExecutionTimeMs(endTime - startTime);
            execution.setRowCount(getRowCount(result));
            sqlExecutionMapper.insert(execution);
            
            return result;
        } catch (Exception e) {
            // 记录失败执行信息
            long endTime = System.currentTimeMillis();
            execution.setIsSuccess(false);
            execution.setExecutionTimeMs(endTime - startTime);
            execution.setErrorMessage(e.getMessage());
            sqlExecutionMapper.insert(execution);
            
            if (e instanceof DataAccessException) {
                throw new QueryExecutionException("查询执行失败: " + e.getCause().getMessage(), e);
            } else {
                throw e;
            }
        }
    }

    private Map<String, Object> executeSafeQuery(String sql, JdbcTemplate jdbcTemplate) {
        String sanitizedSql = sql.replace(";", "") + " LIMIT " + (MAX_ROWS + 1);

        List<Map<String, Object>> results = jdbcTemplate.queryForList(sanitizedSql);

        boolean truncated = results.size() > MAX_ROWS;
        List<Map<String, Object>> finalResults = results.stream()
                .limit(MAX_ROWS)
                .collect(Collectors.toList());

        Set<String> columns = finalResults.stream()
                .findFirst()
                .map(Map::keySet)
                .map(LinkedHashSet::new)
                .orElseGet(LinkedHashSet::new);

        Map<String, Object> resultMap = new LinkedHashMap<>();
        resultMap.put("columns", columns);
        resultMap.put("data", finalResults);
        if (truncated) {
            resultMap.put("warning", "结果超过" + MAX_ROWS + "条，已自动截断");
        }
        return resultMap;
    }

    private void validateSql(String sql) {
        String normalizedSql = sql.toUpperCase().replaceAll("\\s+", " ");

        validateStructure(normalizedSql);
        validateKeywords(normalizedSql);
        validateComments(sql);
    }

    private void validateStructure(String sql) {
        if (!sql.startsWith("SELECT")) {
            throw new SecurityViolationException("仅允许执行SELECT查询");
        }
        if (sql.contains(";")) {
            throw new SecurityViolationException("检测到多语句执行风险");
        }
    }

    private void validateKeywords(String sql) {
        boolean hasForbidden = FORBIDDEN_KEYWORDS.stream()
                .anyMatch(keyword -> sql.contains(keyword));

        if (hasForbidden) {
            String forbiddenWord = FORBIDDEN_KEYWORDS.stream()
                    .filter(sql::contains)
                    .findFirst()
                    .orElse("未知关键字");
            throw new SecurityViolationException("检测到禁用关键字: " + forbiddenWord);
        }
    }

    private void validateComments(String originalSql) {
        if (SQL_COMMENT_PATTERN.matcher(originalSql).find()) {
            throw new SecurityViolationException("SQL包含可疑注释符号");
        }
    }

    // 自定义异常类
    public static class QueryExecutionException extends RuntimeException {
        public QueryExecutionException(String message, Throwable cause) {
            super(message, cause);
        }
    }

    public static class SecurityViolationException extends RuntimeException {
        public SecurityViolationException(String message) {
            super(message);
        }
    }
} 